# coding:utf-8
import pandas as pd
import datetime
import pymysql
import redis


class ReadExcel():

    def __init__(self):
        self.now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        HOST = 'oyo-scrapy-database.mysql.rds.aliyuncs.com'
        PORT = 3306
        USER = 'scrapy'
        PASSWD = 'Podf&T$2'
        DB = 'spider'
        CHARSET = 'utf8mb4'

        self.conn = pymysql.connect(host=HOST, port=PORT, user=USER, passwd=PASSWD, db=DB, charset=CHARSET)
        self.cur = self.conn.cursor()

        RHOST = '10.200.20.200'
        RPORT = 6379
        RPASSWORD = 'zhangym930920'
        RDB = 1
        self.r = redis.Redis(host=RHOST, port=RPORT, password=RPASSWORD, db=RDB, charset='utf-8', decode_responses=True)

    def get_excel(self,date_,no):
        file_name='2019030821_otaaccount.xlsx'
        try:
            data = pd.read_excel(file_name)
        except Exception as err:
            print(err)
            print('未找到文件')
            return
        for index, row in data.iterrows():

            sync_account_id=row[0]
            sync_username=row[3]
            sync_password=row[4]
            sync_status=row[1]
            sync_group_account=row[5]
            sync_ota_channel_en=row[2]
            url=row[6]
            ota_channel_en=''
            if sync_ota_channel_en=='Meituan':
                ota_channel_en='meituan'
            elif sync_ota_channel_en=='Ctrip':
                ota_channel_en = 'ctrip'
            elif sync_ota_channel_en == 'Qunar':
                ota_channel_en = 'qunar'
            elif sync_ota_channel_en == 'Elong':
                ota_channel_en = 'elong'
            elif sync_ota_channel_en == 'Alitrip (Fliggy)':
                ota_channel_en = 'fliggy'
            else:
                continue
            create_time=self.now
            batch_no=date_+no


            print(sync_account_id,sync_username,sync_password,sync_status,sync_group_account,sync_ota_channel_en,ota_channel_en,create_time,batch_no)
            sql="insert into ota_back_account_info (sync_account_id,sync_username,sync_password,sync_status,sync_group_account,sync_ota_channel_en,ota_channel_en,create_time,batch_no,type,url) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(sync_account_id,sync_username,str(sync_password).replace('\'','\'\'').replace('\\','\\\\'),sync_status,sync_group_account,sync_ota_channel_en,ota_channel_en,create_time,batch_no,0,url)

            self.cur.execute(sql)
            self.conn.commit()


    def get_users(self,date_,no):
        # 携程
        sql = "SELECT sync_username,sync_password,CRS_ID,batch_no from ota_back_account_info where batch_no='%s' and ota_channel_en='ctrip' and verify_time is null"%(date_+no)
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        self.users_redis(datas,'ctrip')

        # 美团
        sql = "SELECT sync_username,sync_password,CRS_ID,batch_no from ota_back_account_info where batch_no='%s' and ota_channel_en='meituan' and verify_time is null" % (date_ + no)
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        self.users_redis(datas,'meituan')

        # 艺龙
        sql = "SELECT sync_username,sync_password,CRS_ID,batch_no from ota_back_account_info where batch_no='%s' and ota_channel_en='elong' and verify_time is null" % (date_ + no)
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        self.users_redis(datas, 'elong')

        # 去哪儿
        sql = "SELECT sync_username,sync_password,CRS_ID,batch_no from ota_back_account_info where batch_no='%s' and ota_channel_en='qunar' and verify_time is null" % (date_ + no)
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        self.users_redis(datas, 'qunar')

    def users_redis(self,datas,ota_channel):
        for data in datas:
            Ctrip_Username = str(data[0]).strip().replace(' ','')
            Ctrip_Password = str(data[1]).strip().replace(' ','')
            CRS_ID = data[2]
            batch_no = data[3]

            print(Ctrip_Username, Ctrip_Password, CRS_ID, batch_no)
            self.r.rpush('hotel_users_'+ota_channel, (Ctrip_Username, Ctrip_Password, CRS_ID, batch_no))

if __name__ == '__main__':
    # batch_no = datetime.datetime.now().strftime('%Y%m%d')
    # no = datetime.datetime.now().strftime('%H')
    batch_no = '20190308'
    no = '21'
    begin=ReadExcel()
    begin.get_excel(batch_no, no)
    begin.get_users(batch_no, no)